package com.example.gaoyang.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.gaoyang.constant.Constant;
import com.example.gaoyang.domain.ConstructionPlan;
import com.example.gaoyang.domain.Station;
import com.example.gaoyang.domain.Way;
import com.example.gaoyang.domain.WayDirection;
import com.example.gaoyang.domain.export.ConstructionPlanExport;
import com.example.gaoyang.domain.request.ConstructionPlanRequest;
import com.example.gaoyang.domain.request.PileLonLatRequest;
import com.example.gaoyang.domain.vo.ConstructionPlanVO;
import com.example.gaoyang.domain.vo.PileLonLatVO;
import com.example.gaoyang.exception.InvalidParameterException;
import com.example.gaoyang.mapper.ConstructionPlanMapper;
import com.example.gaoyang.request.Result;
import com.example.gaoyang.service.*;
import com.example.gaoyang.util.DateStringUtil;
import com.example.gaoyang.util.PileUtil;
import com.example.gaoyang.util.TokenUtil;
import com.example.gaoyang.wrapper.ConstructionPlanWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
@Transactional
public class ConstructionPlanServiceImpl extends ServiceImpl<ConstructionPlanMapper, ConstructionPlan> implements ConstructionPlanService {

    @Resource
    private WayService wayService;

    @Resource
    private WayDirectionService wayDirectionService;

    @Resource
    private StationService stationService;

    @Resource
    private PileLonLatService pileLonLatService;

    @Override
    public void addConstructionPlan(ConstructionPlanRequest constructionPlanRequest) {
        ConstructionPlan constructionPlan = BeanUtil.copyProperties(constructionPlanRequest, ConstructionPlan.class);
        // 道路编号必填
        String wayId = constructionPlanRequest.getWayId();
        if (StringUtils.isBlank(wayId)) {
            throw new InvalidParameterException("道路编号必填");
        }
        // 发生位置必填
        Integer occurLocationId = constructionPlanRequest.getOccurLocationId();
        if (occurLocationId == null) {
            throw new InvalidParameterException("发生位置必选");
        }
        // 如果发生位置事按路段
        if (Constant.INTEGER_ONE.equals(occurLocationId)) {
            // 施工开始桩号
            String pileStartCode = constructionPlanRequest.getPileStartCode();
            // 判断是否满足格式
            if (!PileUtil.isValidPileCode(pileStartCode)) {
                throw new InvalidParameterException("施工开始桩号格式错误");
            }
            // 施工结束桩号
            String pileEndCode = constructionPlanRequest.getPileEndCode();
            // 判断是否满足格式
            if (!PileUtil.isValidPileCode(pileEndCode)) {
                throw new InvalidParameterException("施工结束桩号格式错误");
            }
            Integer pileStartNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(pileStartCode));
            constructionPlan.setPileStartNumber(pileStartNumber);
            Integer pileEndNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(pileEndCode));
            constructionPlan.setPileEndNumber(pileEndNumber);
        }
        // 创建时间
        constructionPlan.setCreateTime(new Date());
        // 获取当前登录用户ID
        Long currentUserId = TokenUtil.parseUserIdFromCurrentRequest();
        constructionPlan.setCreateUserId(currentUserId);
        this.save(constructionPlan);
    }

    @Override
    public void batchDeleteConstructionPlan(List<Long> ids) {
        // id列表不为空
        if (CollectionUtil.isNotEmpty(ids)) {
            this.removeByIds(ids);
        }
    }

    @Override
    public List<ConstructionPlanVO> getConstructionPlanList(ConstructionPlanRequest constructionPlanRequest) {
        LambdaQueryWrapper<ConstructionPlan> wrapper = this.createWrapper(constructionPlanRequest);
        List<ConstructionPlanVO> list = BeanUtil.copyToList(this.list(wrapper), ConstructionPlanVO.class);
        this.prefectPlanList(list);
        return list;
    }

    @Override
    public IPage<ConstructionPlanVO> getConstructionPlanPageList(IPage<ConstructionPlan> page, ConstructionPlanRequest constructionPlanRequest) {
        page.setCurrent(constructionPlanRequest.getPageNum());
        page.setSize(constructionPlanRequest.getPageSize());
        IPage<ConstructionPlan> constructionPlanPage = this.page(page, this.createWrapper(constructionPlanRequest));
        // 转VO
        IPage<ConstructionPlanVO> constructionPlanVOPage = new ConstructionPlanWrapper().pageVO(constructionPlanPage);
        this.prefectPlanList(constructionPlanVOPage.getRecords());
        return constructionPlanVOPage;
    }

    @Override
    public void updateConstructionPlan(ConstructionPlanRequest constructionPlanRequest) {
        ConstructionPlan constructionPlan = BeanUtil.copyProperties(constructionPlanRequest, ConstructionPlan.class);
        // 道路编号必填
        String wayId = constructionPlanRequest.getWayId();
        if (StringUtils.isBlank(wayId)) {
            throw new InvalidParameterException("道路编号必填");
        }
        // 发生位置必填
        Integer occurLocationId = constructionPlanRequest.getOccurLocationId();
        if (occurLocationId == null) {
            throw new InvalidParameterException("发生位置必选");
        }
        // id必传
        Long id = constructionPlanRequest.getId();
        if (id == null) {
            throw new InvalidParameterException("id必传");
        }
        // 如果发生位置事按路段
        if (Constant.INTEGER_ONE.equals(occurLocationId)) {
            // 施工开始桩号
            String pileStartCode = constructionPlanRequest.getPileStartCode();
            // 判断是否满足格式
            if (!PileUtil.isValidPileCode(pileStartCode)) {
                throw new InvalidParameterException("施工开始桩号格式错误");
            }
            // 施工结束桩号
            String pileEndCode = constructionPlanRequest.getPileEndCode();
            // 判断是否满足格式
            if (!PileUtil.isValidPileCode(pileEndCode)) {
                throw new InvalidParameterException("施工结束桩号格式错误");
            }
            Integer pileStartNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(pileStartCode));
            constructionPlan.setPileStartNumber(pileStartNumber);
            Integer pileEndNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(pileEndCode));
            constructionPlan.setPileEndNumber(pileEndNumber);
        }
        // 修改时间
        constructionPlan.setUpdateTime(new Date());
        // 获取当前登录用户ID
        Long currentUserId = TokenUtil.parseUserIdFromCurrentRequest();
        constructionPlan.setCreateUserId(currentUserId);
        this.updateById(constructionPlan);
    }


    @Override
    public void importConstructionPlan(MultipartFile file) {
        XSSFWorkbook workbook = null;
        InputStream is = null;
        try {
            // 文件是xlsx格式
            if (StringUtils.isNotEmpty(file.getOriginalFilename()) && !file.getOriginalFilename().endsWith(Constant.XLSX)) {
                throw new InvalidParameterException("文件格式错误,请调整为xlsx格式");
            }
            // 获取所有道路列表
            List<Way> wayList = wayService.list();
            List<String> wayCodeList = wayList.stream().map(Way::getId).collect(Collectors.toList());
            String wayCodesStr = String.join(",", wayCodeList);
            // 道路方向列表
            List<WayDirection> directionList = wayDirectionService.list();
            // 道路-道路对应的方向map
            Map<String, List<WayDirection>> wayDirectionMap = directionList.stream()
                    .collect(Collectors.groupingBy(WayDirection::getWay));
            // 道路收费站列表
            List<Station> stationList = stationService.list();
            // 道路-道路对应的收费站名称map
            Map<String, List<Station>> wayStationMap = stationList.stream()
                    .collect(Collectors.groupingBy(Station::getWayId));
            // 批量新增列表
            List<ConstructionPlan> insertList = new ArrayList<>();
            is = file.getInputStream();
            workbook = new XSSFWorkbook(is);
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 获取表头行的最后一列数
            Row headerRow = sheet.getRow(0);
            int maxColumnCount = headerRow.getLastCellNum();
            // 嵌套循环遍历每一个单元格，从第二行开始
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                ConstructionPlan constructionPlan = new ConstructionPlan();
                // 从第二行开始，索引从1开始
                Row row = sheet.getRow(i);
                if (row != null) {
                    for (int j = 0; j < maxColumnCount; j++) {
                        Cell cell = row.getCell(j);
                        // 第一列代表计划开始时间
                        if (j == 0) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,计划开始时间不能为空,请重新填写", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            // 计划开始时间不为空且需要满足格式
                            if (StringUtils.isEmpty(cellValue) || !DateStringUtil.isValidDateTime(cellValue)) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,计划开始时间不为空且需要满足年月日时分秒格式,请重新填写", i + 1, j + 1));
                            }
                            // 计划开始时间
                            Date planStartTime = DateStringUtil.parseStringToDate(cellValue);
                            constructionPlan.setPlanStartTime(planStartTime);
                        }
                        // 第二列代表计划结束时间
                        if (j == 1) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,计划结束时间不能为空,请重新填写", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            // 计划结束时间不为空且需要满足格式
                            if (StringUtils.isEmpty(cellValue) || !DateStringUtil.isValidDateTime(cellValue)) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,计划结束时间不为空且需要满足年月日时分秒格式,请重新填写", i + 1, j + 1));
                            }
                            Date planEndTime = DateStringUtil.parseStringToDate(cellValue);
                            constructionPlan.setPlanEndTime(planEndTime);
                        }
                        // 第三列代表实际开始时间
                        if (j == 2) {
                            if (cell != null) {
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isNotEmpty(cellValue)) {
                                    // 如果填了,那么需要满足格式
                                    if (!DateStringUtil.isValidDateTime(cellValue)) {
                                        throw new InvalidParameterException(String.format("第%d行,第%d列,实际开始时间需要满足年月日时分秒格式,请重新填写", i + 1, j + 1));
                                    }
                                    Date actualStartTime = DateStringUtil.parseStringToDate(cellValue);
                                    constructionPlan.setActualStartTime(actualStartTime);
                                }
                            }
                        }
                        // 第四列代表实际结束时间
                        if (j == 3) {
                            if (cell != null) {
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isNotEmpty(cellValue)) {
                                    // 如果填了,那么需要满足格式
                                    if (!DateStringUtil.isValidDateTime(cellValue)) {
                                        throw new InvalidParameterException(String.format("第%d行,第%d列,实际结束时间需要满足年月日时分秒格式,请重新填写", i + 1, j + 1));
                                    }
                                    Date actualEndTime = DateStringUtil.parseStringToDate(cellValue);
                                    constructionPlan.setActualEndTime(actualEndTime);
                                }
                            }
                        }
                        // 第五列代表施工状态
                        if (j == 4) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,施工状态不能为空,请重新填写", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            if (StringUtils.isNotEmpty(cellValue)) {
                                // 如果施工状态填写了,那么只能填1,2,3
                                if (!this.isValidConstructionStatus(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,施工状态只能填1-未开始,2-进行中,3-已完成,请重新填写", i + 1, j + 1));
                                }
                            }
                            constructionPlan.setConstructionStatus(Integer.parseInt(cellValue));
                        }
                        // 第六列代表道路ID
                        if (j == 5) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,道路编码不能为空,请重新填写", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            if (StringUtils.isEmpty(cellValue)) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,道路编码不能为空,请重新填写", i + 1, j + 1));
                            }
                            // 道路ID必须存在
                            Way way = wayList.stream().filter(item -> item.getId().equals(cellValue)).findFirst().orElse(null);
                            if (way == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,道路编码不存在,道路编码必须在[" + wayCodesStr + "]范围内,请重新填写", i + 1, j + 1));
                            }
                            constructionPlan.setWayId(way.getId());
                        }
                        // 第七列代表发生位置
                        if (j == 6) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置不能为空,请重新填写", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            // 发生位置必填,且必须要在1,2,3中
                            if (StringUtils.isEmpty(cellValue) || !this.isValidOccurLocation(cellValue)) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置必填,发生位置只能填1-按路段,2-按收费站,3-其他,请重新填写", i + 1, j + 1));
                            }
                            constructionPlan.setOccurLocationId(Integer.parseInt(cellValue));
                        }
                        // 第八列代表方向
                        if (j == 7) {
                            // 如果发声位置是按路段,那么必须填写
                            if (Constant.INTEGER_ONE.equals(constructionPlan.getOccurLocationId())) {
                                if (cell == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填方向", i + 1, j + 1));
                                }
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isEmpty(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填方向", i + 1, j + 1));
                                }
                                // 路段编号
                                String wayId = constructionPlan.getWayId();
                                // 根据wayId获取与之对应的方向
                                List<WayDirection> wayDirections = wayDirectionMap.get(wayId);
                                // 所填写的方向要在列表内
                                WayDirection wayDirection = wayDirections.stream().filter(item -> item.getDirection().equals(cellValue)).findFirst().orElse(null);
                                // 方向列表拼接的字符串
                                String wayDirectionsStr = wayDirections.stream().map(WayDirection::getDirection).collect(Collectors.joining(","));
                                if (wayDirection == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填正确的方向,方向必须在[" + wayDirectionsStr + "]范围内,请重新填写", i + 1, j + 1));
                                }
                                constructionPlan.setDirectionId(wayDirection.getDirectionId());
                            }
                        }
                        // 第九列代表施工开始桩号
                        if (j == 8) {
                            // 如果发声位置是按路段,那么必须填写
                            if (Constant.INTEGER_ONE.equals(constructionPlan.getOccurLocationId())) {
                                if (cell == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填施工开始桩号", i + 1, j + 1));
                                }
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isEmpty(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填施工开始桩号", i + 1, j + 1));
                                }
                                // 判断开始桩号的填写格式是否满足条件
                                if (!PileUtil.isValidPileCode(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,施工开始桩号格式不正确应为:Kxxx+yyy", i + 1, j + 1));
                                }
                                constructionPlan.setPileStartCode(cellValue);
                                // 根据施工开始桩号计算开始桩号数值
                                Integer pileStartNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(cellValue));
                                constructionPlan.setPileStartNumber(pileStartNumber);
                            }
                        }
                        // 第十列代表施工结束桩号
                        if (j == 9) {
                            // 如果发声位置是按路段,那么必须填写
                            if (Constant.INTEGER_ONE.equals(constructionPlan.getOccurLocationId())) {
                                if (cell == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填施工结束桩号", i + 1, j + 1));
                                }
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isEmpty(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,请填施工结束桩号", i + 1, j + 1));
                                }
                                // 判断结束桩号的填写格式是否满足条件
                                if (!PileUtil.isValidPileCode(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按路段,施工结束桩号格式不正确应为:Kxxx+yyy", i + 1, j + 1));
                                }
                                constructionPlan.setPileEndCode(cellValue);
                                // 根据施工结束桩号计算结束桩号数值
                                Integer pileEndNumber = Math.toIntExact(PileUtil.parsePileCodeToNumber(cellValue));
                                constructionPlan.setPileEndNumber(pileEndNumber);
                            }
                        }
                        // 第十一列代表收费站名称
                        if (j == 10) {
                            // 如果发声位置是按收费站,那么必须填写
                            if (Constant.INTEGER_TWO.equals(constructionPlan.getOccurLocationId())) {
                                if (cell == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按收费站,请填收费站名称", i + 1, j + 1));
                                }
                                String cellValue = this.getCellValue(cell);
                                if (StringUtils.isEmpty(cellValue)) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按收费站,请填收费站名称", i + 1, j + 1));
                                }
                                // 道路id
                                String wayId = constructionPlan.getWayId();
                                // 根据道路id获取收费站列表
                                List<Station> stations = wayStationMap.get(wayId);
                                Station station = stations.stream().filter(item -> item.getStationName().equals(cellValue)).findFirst().orElse(null);
                                // 收费站名称列表拼接的字符串
                                String stationsStr = stations.stream().map(Station::getStationName).collect(Collectors.joining(","));
                                if (station == null) {
                                    throw new InvalidParameterException(String.format("第%d行,第%d列,发生位置为按收费站,请填正确的收费站名称,收费站名称必须在[" + stationsStr + "]范围内,请重新填写", i + 1, j + 1));
                                }
                                constructionPlan.setStationId(station.getId());
                            }
                        }
                        // 第十二列代表其他备注
                        if (j == 11) {
                            if (cell != null) {
                                String cellValue = this.getCellValue(cell);
                                constructionPlan.setOtherContent(cellValue);
                            }
                        }
                        // 第十三列为施工联系人
                        if (j == 12) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,请填写施工联系人", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            constructionPlan.setConstructionUserName(cellValue);
                        }
                        // 第十四列是工联系人手机号
                        if (j == 13) {
                            if (cell == null) {
                                throw new InvalidParameterException(String.format("第%d行,第%d列,请填写工联系人手机号", i + 1, j + 1));
                            }
                            String cellValue = this.getCellValue(cell);
                            constructionPlan.setConstructionUserPhoneNumber(cellValue);
                        }
                        // 第十五列代表施工内容
                        if (j == 14) {
                            if (cell != null) {
                                String cellValue = this.getCellValue(cell);
                                constructionPlan.setConstructionContent(cellValue);
                            }
                        }
                        // 第十六列代表施工项目
                        if (j == 15) {
                            if (cell != null) {
                                String cellValue = this.getCellValue(cell);
                                constructionPlan.setConstructionProject(cellValue);
                            }
                        }
                    }
                    // 创建时间
                    constructionPlan.setCreateTime(new Date());
                    // 获取当前登录用户ID
                    Long currentUserId = TokenUtil.parseUserIdFromCurrentRequest();
                    constructionPlan.setCreateUserId(currentUserId);
                    insertList.add(constructionPlan);
                }
            }
            if (CollectionUtil.isNotEmpty(insertList)) {
                this.saveOrUpdateBatch(insertList);
            }
        } catch (Exception e) {
            log.error("施工计划导入异常", e);
            if (e instanceof InvalidParameterException) {
                // 重新抛出业务异常
                throw (InvalidParameterException) e;
            } else {
                // 包装为业务异常
                throw new InvalidParameterException("导入失败: " + e.getMessage());
            }
        } finally {
            // 确保资源被正确关闭
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.warn("关闭workbook时发生异常", e);
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    log.warn("关闭输入流时发生异常", e);
                }
            }
        }
    }

    @Override
    public ConstructionPlanVO getConstructionPlanDetail(ConstructionPlanRequest constructionPlanRequest) {
        // id必传
        Long id = constructionPlanRequest.getId();
        if (id == null) {
            throw new InvalidParameterException("id不能为空");
        }
        ConstructionPlan constructionPlan = this.getById(id);
        if (constructionPlan == null) {
            throw new InvalidParameterException("未找到对应的施工计划");
        }
        ConstructionPlanVO constructionPlanVO = BeanUtil.copyProperties(constructionPlan, ConstructionPlanVO.class);
        List<ConstructionPlanVO> list = Collections.singletonList(constructionPlanVO);
        // 完善列表赋值
        this.prefectPlanList(list);
        return list.get(0);
    }

    @Override
    public void exportConstructionPlanTemplate(HttpServletResponse response) {
        try {
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 文件名编码处理
            String fileName = "施工计划导出_" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
            String encodedFileName = URLEncoder.encode(fileName, String.valueOf(StandardCharsets.UTF_8)).replace("+", "%20");
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + encodedFileName + ".xlsx");
            // 假数据
            List<ConstructionPlanExport> dataList = this.getMockData();
            // 写入Excel
            EasyExcel.write(response.getOutputStream(), ConstructionPlanExport.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 自动列宽
                    .sheet("施工计划")
                    .doWrite(dataList);
        } catch (Exception e) {
            log.error("导出施工计划模板异常", e);
            throw new InvalidParameterException("导出施工计划模板异常: " + e.getMessage());
        }
    }

    private String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 直接获取完整的日期时间值
                    Date date = cell.getDateCellValue();
                    LocalDateTime localDateTime = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                    return localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                } else {
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == Math.floor(numericValue) && !Double.isInfinite(numericValue)) {
                        // 是整数
                        return String.valueOf((long) numericValue);
                    } else {
                        // 是浮点数
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }


    private boolean isValidConstructionStatus(String constructionStatusStr) {
        if (StringUtils.isNotEmpty(constructionStatusStr)) {
            return constructionStatusStr.matches("^[1-3]$");
        }
        return true;
    }

    private boolean isValidOccurLocation(String constructionStatusStr) {
        if (StringUtils.isNotEmpty(constructionStatusStr)) {
            return constructionStatusStr.matches("^[1-3]$");
        }
        return true;
    }

    /**
     * 模拟数据
     */
    private List<ConstructionPlanExport> getMockData() {
        List<ConstructionPlanExport> list = new ArrayList<>();
        // 示例1：按路段
        ConstructionPlanExport plan1 = new ConstructionPlanExport();
        plan1.setPlanStartTime("2025-03-01 00:00:00");
        plan1.setPlanEndTime("2025-03-01 20:00:00");
        plan1.setActualStartTime("2025-03-01 03:00:00");
        plan1.setActualEndTime("2025-03-01 17:00:00");
        plan1.setConstructionStatus(1);
        plan1.setRoadCode("G15");
        plan1.setLocationType(1);
        plan1.setDirection("甬向");
        plan1.setStartPileNo("K1460+050");
        plan1.setEndPileNo("K1461+300");
        plan1.setContactPerson("张三");
        plan1.setContactPhone("18888888888");
        plan1.setConstructionContent("张三施工内容");
        plan1.setConstructionProject("张三施工项目");
        list.add(plan1);
        // 示例2：按收费站
        ConstructionPlanExport plan2 = new ConstructionPlanExport();
        plan2.setPlanStartTime("2025-03-01 00:00:00");
        plan2.setPlanEndTime("2025-03-01 20:00:00");
        plan2.setActualStartTime("2025-03-01 03:00:00");
        plan2.setActualEndTime("2025-03-01 17:00:00");
        plan2.setConstructionStatus(1);
        plan2.setRoadCode("G15");
        plan2.setLocationType(2);
        plan2.setTollStationName("南接线全线所站");
        plan2.setContactPerson("李四");
        plan2.setContactPhone("19999999999");
        plan2.setConstructionContent("李四施工内容");
        plan2.setConstructionProject("李四施工项目");
        list.add(plan2);
        // 示例3：其他类型
        ConstructionPlanExport plan3 = new ConstructionPlanExport();
        plan3.setPlanStartTime("2025-03-01 00:00:00");
        plan3.setPlanEndTime("2025-03-01 20:00:00");
        plan3.setActualStartTime("2025-03-01 03:00:00");
        plan3.setActualEndTime("2025-03-01 17:00:00");
        plan3.setConstructionStatus(1);
        plan3.setRoadCode("G15");
        plan3.setLocationType(3);
        plan3.setOtherRemarks("其他备注");
        plan3.setContactPerson("王五");
        plan3.setContactPhone("17777777777");
        plan3.setConstructionContent("王五施工内容");
        plan3.setConstructionProject("王五施工项目");
        list.add(plan3);
        return list;
    }

    /**
     * 预处理数据
     * @param planList
     */
    private void prefectPlanList(List<ConstructionPlanVO> planList) {
        // 道路列表
        List<Way> ways = wayService.list();
        // 道路id-简称map
        Map<String, String> wayIdShortNameMap = ways.stream()
                .collect(Collectors.toMap(Way::getId, Way::getWayHighSpeedName));
        // 道路方向列表
        List<WayDirection> wayDirections = wayDirectionService.list();
        // 收费站列表
        List<Station> stations = stationService.list();
        // 收费站id-名称map
        Map<String, String> stationIdNameMap = stations.stream()
                .collect(Collectors.toMap(Station::getId, Station::getStationName));
        planList.forEach(plan -> {
            // 道路编码
            String wayId = plan.getWayId();
            if (StringUtils.isNotEmpty(wayId)) {
                // 道路简称
                plan.setWayShortName(wayIdShortNameMap.get(wayId));
            }
            // 方向id
            String directionId = plan.getDirectionId();
            // 道路名称
            WayDirection wayDirection = wayDirections.stream()
                    .filter(item -> StringUtils.equals(item.getWay(), wayId) && StringUtils.equals(item.getDirectionId(), directionId))
                    .findFirst()
                    .orElse(null);
            plan.setDirectionName(wayDirection != null ? wayDirection.getDirection() : "");
            // 收费站id
            String stationId = plan.getStationId();
            if (StringUtils.isNotEmpty(stationId)) {
                plan.setStationName(stationIdNameMap.get(stationId));
            }
            // 开始桩号
            String pileStartCode = plan.getPileStartCode();
            if (StringUtils.isNotEmpty(pileStartCode)) {
                // 截取千米桩号
                String pileStartKM = pileStartCode.substring(1, pileStartCode.indexOf("+"));
                plan.setPileStartKM(Integer.parseInt(pileStartKM));
                // 截取百米桩号
                String pileStartHM = pileStartCode.substring(pileStartCode.indexOf("+") + 1);
                plan.setPileStartHM(Integer.parseInt(pileStartHM));
            }
            // 结束桩号
            String pileEndCode = plan.getPileEndCode();
            if (StringUtils.isNotEmpty(pileEndCode)) {
                // 截取千米桩号
                String pileEndKM = pileEndCode.substring(1, pileEndCode.indexOf("+"));
                plan.setPileEndKM(Integer.parseInt(pileEndKM));
                // 截取百米桩号
                String pileEndHM = pileEndCode.substring(pileEndCode.indexOf("+") + 1);
                plan.setPileEndHM(Integer.parseInt(pileEndHM));
            }
            // 根据路段和桩号获取最靠近点的经纬度
            PileLonLatRequest request = new PileLonLatRequest();
            request.setCode(wayId);
            request.setRoadNo(wayId);
            request.setMileage(pileStartCode);
            PileLonLatVO closestPileLonLat = pileLonLatService.getClosestPileLonLat(request);
            // 经度
            plan.setLatitude(closestPileLonLat != null ? closestPileLonLat.getLon() : "");
            // 纬度
            plan.setLongitude(closestPileLonLat != null ? closestPileLonLat.getLat() : "");
        });
    }

    private LambdaQueryWrapper<ConstructionPlan> createWrapper(ConstructionPlanRequest constructionPlanRequest) {
        return new LambdaQueryWrapper<ConstructionPlan>()
                .eq(constructionPlanRequest.getOccurLocationId() != null, ConstructionPlan::getOccurLocationId, constructionPlanRequest.getOccurLocationId())
                .eq(StringUtils.isNotEmpty(constructionPlanRequest.getWayId()), ConstructionPlan::getWayId, constructionPlanRequest.getWayId())
                .eq(StringUtils.isNotEmpty(constructionPlanRequest.getDirectionId()), ConstructionPlan::getDirectionId, constructionPlanRequest.getDirectionId())
                .eq(StringUtils.isNotEmpty(constructionPlanRequest.getStationId()), ConstructionPlan::getStationId, constructionPlanRequest.getStationId())
                .like(StringUtils.isNotEmpty(constructionPlanRequest.getPileStartCode()), ConstructionPlan::getPileStartCode, constructionPlanRequest.getPileStartCode())
                .like(StringUtils.isNotEmpty(constructionPlanRequest.getPileEndCode()), ConstructionPlan::getPileEndCode, constructionPlanRequest.getPileEndCode())
                .like(StringUtils.isNotEmpty(constructionPlanRequest.getConstructionContent()), ConstructionPlan::getConstructionContent, constructionPlanRequest.getConstructionContent())
                .like(StringUtils.isNotEmpty(constructionPlanRequest.getConstructionUserName()), ConstructionPlan::getConstructionUserName, constructionPlanRequest.getConstructionUserName())
                // 计划开始时间大于等于planStartTimeStart
                .ge(StringUtils.isNotEmpty(constructionPlanRequest.getPlanStartTimeStart()), ConstructionPlan::getPlanStartTime, constructionPlanRequest.getPlanStartTimeStart())
                // 计划开始时间小于等于planStartTimeEnd
                .le(StringUtils.isNotEmpty(constructionPlanRequest.getPlanStartTimeEnd()), ConstructionPlan::getPlanStartTime, constructionPlanRequest.getPlanStartTimeEnd())
                // 计划开始时间倒序
                .orderByDesc(ConstructionPlan::getPlanStartTime);
    }
}
